package com.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.mk.type.IntegerType;
import org.mk.type.Type;

import com.caucho.hessian.HessianException;
import com.caucho.hessian.server.HessianServlet;
import com.db.connection.DBCPConnectionManager2;
import com.db.connection.DBCPPostgresManager;
import com.db.domain.Communication;
import com.db.domain.Record;

public class CCServiceImpl extends HessianServlet implements CCService {

	/**
	 * 某个店请求 1.将请求缓存 3.取得该店的响应返回给该店
	 * 
	 * @param aCommunication
	 * @return
	 */
	public List<Communication> client2Client(int storeId, int recordId) {
		 List<Communication>  aList = getTree(storeId, recordId);
		return aList;
	}
	
	public void sendRequest(List<Communication> list) {
		insert(list);
	}

	
	public List<Communication> doResponse(List<Communication>  requestList) {

		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn.prepareStatement("update  communication set response = ?, flag = 'Y' where id = ?");
			Type type = new IntegerType();
			for(Communication aCommunication : requestList) {
				type.nullSafeSet(pstmt, aCommunication.id, 1);
				pstmt.setString(1, aCommunication.response);
				type.nullSafeSet(pstmt, aCommunication.id, 2);
				pstmt.addBatch();
			}			
			pstmt.executeBatch();	
			pstmt.getConnection().commit();
		} catch (Exception ex) {
			System.out.println("ErrorServer : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

		return aList;
	}

	
	
	/**
	 * 取得响应
	 * @param storeId
	 * @param list
	 * @return
	 */
	public List<Communication> getResponse(long storeId) {
		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {										
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn.prepareStatement("select * from communication where client_to = ?  and flag = 'N'  order by id");
			Type type = new IntegerType();
			type.nullSafeSet(pstmt, storeId, 1);
			rs = pstmt.executeQuery();
			System.out.println("店" + storeId + "getResponse");
			while (rs.next()) {
				//System.out.println("店" + storeId + "取得响应");
				Communication aCommunication = new Communication();
				String atype = rs.getString("type");
				String request = rs.getString("request");
				String response = rs.getString("response");
				long clientFrom = rs.getLong("client_from");
				long clientTo = rs.getLong("client_to");
				aCommunication.id = rs.getLong("id");
				aCommunication.clientFrom = clientFrom;
				aCommunication.clientTo = clientTo;
				aCommunication.request = request;
				aCommunication.response = response;
				aList.add(aCommunication);
			}
		} catch (Exception ex) {
			System.out.println("ErrorServer : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

		return aList;
	}

	/**
	 * 记录是否已经存在外键约束
	 * @param storeId
	 * @param recordId
	 * @return
	 * Aug 8, 2009
	 */
	public Integer recordExist(int storeId, long recordId) {
		Integer id = null;
		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn.prepareStatement("select id from communication where parent = 0 and  client_from = ? and record = ?  order by parent");
			Type type = new IntegerType();
			type.nullSafeSet(pstmt, storeId, 1);
			type.nullSafeSet(pstmt, recordId, 2);
			rs = pstmt.executeQuery();			
			if (rs.next()) {
				return new Integer(rs.getInt(1));
			}
		} catch (Exception ex) {
			System.out.println("ErrorServer : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

		return null;
	}
	
	
	
	private List<Communication> getTree(int storeId, long recordId) {
		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn.prepareStatement("select * from communication where client_from = ? and record = ?  order by parent");
			Type type = new IntegerType();
			type.nullSafeSet(pstmt, storeId, 1);
			type.nullSafeSet(pstmt, recordId, 2);
			rs = pstmt.executeQuery();
			System.out.println("店:"+storeId+"取树："+recordId);
			while (rs.next()) {
				
				Communication aCommunication = new Communication();
				String atype = rs.getString("type");
				String request = rs.getString("request");
				String response = rs.getString("response");
				long clientFrom = rs.getLong("client_from");
				long clientTo = rs.getLong("client_to");
				aCommunication.id = rs.getLong("id");
				aCommunication.clientFrom = clientFrom;
				aCommunication.clientTo = clientTo;
				aCommunication.request = request;
				aCommunication.response = response;
				aCommunication.recordId = recordId;
				aCommunication.storeId = storeId;
				aCommunication.parent =  rs.getLong("parent");
				aList.add(aCommunication);
				
			}
		} catch (Exception ex) {
			System.out.println("ErrorServer : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

		return aList;
	}
	
	
	public boolean getFKFlag(long recordId) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn.prepareStatement("select fk_flag from record where id=?");
			Type type = new IntegerType();
			type.nullSafeSet(pstmt, recordId, 1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				String flag = rs.getString(1);
				if(flag!=null && flag.equalsIgnoreCase("Y")) {
					return true;
				}
			}
		} catch (Exception ex) {
			System.out.println("ErrorServer : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

		return false;
	}
	
	
	
	public void insert(Communication aCommunication)  throws HessianException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet res = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn
					.prepareStatement("insert into communication (client_from, client_to, type, created, request, response, flag, parent, level, record) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

			
				String response = aCommunication.response;
				pstmt.setLong(1, aCommunication.clientFrom);
				pstmt.setLong(2, aCommunication.clientTo);
				pstmt.setString(3, aCommunication.type);
				pstmt.setString(4, aCommunication.created);
				pstmt.setString(5, aCommunication.request);
				pstmt.setString(6, aCommunication.response);
				pstmt.setString(7, aCommunication.flag);
				pstmt.setLong(8, aCommunication.parent);
				pstmt.setLong(9, aCommunication.level);
				pstmt.setLong(10, aCommunication.recordId);
				pstmt.addBatch();
			pstmt.executeUpdate();
			pstmt.getConnection().commit();
		} catch (Exception ex) {
			System.out.println("Error : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

	}
	
	private void insert(List<Communication> list) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet res = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn
					.prepareStatement("insert into communication (client_from, client_to, type, created, request, response, flag, parent, level, record) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

			for (Communication aCommunication : list) {
				String response = aCommunication.response;
				pstmt.setLong(1, aCommunication.clientFrom);
				pstmt.setLong(2, aCommunication.clientTo);
				pstmt.setString(3, aCommunication.type);
				pstmt.setString(4, aCommunication.created);
				pstmt.setString(5, aCommunication.request);
				pstmt.setString(6, aCommunication.response);
				pstmt.setString(7, aCommunication.flag);
				pstmt.setLong(8, aCommunication.parent);
				pstmt.setLong(9, aCommunication.level);
				pstmt.setLong(10, aCommunication.recordId);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.getConnection().commit();

		} catch (Exception ex) {
			System.out.println("Error : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

	}

	
	public void setFlag(List<Communication> list) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet res = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn
					.prepareStatement("update communication set flag = 'Y' where id = ?");
			for (Communication aCommunication : list) {
				pstmt.setLong(1, aCommunication.getId());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.getConnection().commit();

		} catch (Exception ex) {
			System.out.println("Error : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

	}
	
	/**
	 * 取得发送到该店的请求
	 * 
	 * @param storeId
	 * @return
	 */
	public List<Communication> getRequest(long storeId) {
		// select * from communication where id = ?
		// select * from communication where id = ?
		List<Communication> aList = new ArrayList<Communication>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn
					.prepareStatement("select * from communication where client_to = ? and flag = 'N' order by id");
			Type type = new IntegerType();
			type.nullSafeSet(pstmt, storeId, 1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				System.out.println("店" + storeId + "取得请求");
				Communication aCommunication = new Communication();

				String atype = rs.getString("type");
				String request = rs.getString("request");
				String response = rs.getString("response");
				long clientFrom = rs.getLong("client_from");
				long clientTo = rs.getLong("client_to");

				aCommunication.id = rs.getLong("id");
				aCommunication.clientFrom = clientFrom;
				aCommunication.clientTo = clientTo;
				aCommunication.request = request;
				aCommunication.response = response;

				aList.add(aCommunication);
				return aList;
			}
		} catch (Exception ex) {
			System.out.println("ErrorServer : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}

		return aList;
	}

	/**
	 * 发送响应
	 * 
	 * @param list
	 */
	public void sendResponse(List<Communication> list) {
		// select * from communication where id = ?
		// 1.0 修改标
		// 1.1 填充response
		for (Communication aCommunication : list) {
			String response = aCommunication.response;
			doneRecord(aCommunication.id, response);

		}

	}

	public long doneRecord(Long id, String response) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ResultSet res = null;
		try {
			conn = DBCPConnectionManager2.getConnection();
			// 执行SQL语句
			pstmt = conn
					.prepareStatement("update communciation set flag = 'Y', response = ? where id = ? ");
			pstmt.setLong(1, id);
			pstmt.setString(2, response);
			pstmt.executeUpdate();
			pstmt.getConnection().commit();

		} catch (Exception ex) {
			System.out.println("Error : " + ex.toString());
		} finally {
			DBCPConnectionManager2.close(conn, pstmt, rs);

		}
		return 0;
	}

	

	
}
